from . import api_blueprint
from flask import request, json
from app import db
from app.model.score import Score
from app.utils.save_file import SaveFile
import xlrd
import os


@api_blueprint.route('/score/all', methods=['GET'])
def get_all_scores():
    results = Score.query.all()
    return json.dumps(results, default=Score.to_json)


@api_blueprint.route('/score', methods=['GET'])
def get_scores_by_params():
    cos_id = request.values.get('Cos_id')
    stu_id = request.values.get('Stu_id')
    if cos_id is None or stu_id is None:
        return json.dumps({
            'status': 'error',
            'msg': 'param is missing'
        }), 400
    if len(cos_id) > 0 and len(stu_id) > 0:
        results = Score.query.filter(Score.Stu_id.like('%' + stu_id + '%')).filter(
            Score.Cos_id.like('%' + cos_id + '%')).all()
    elif len(cos_id) > 0:
        results = Score.query.filter(Score.Cos_id.like('%' + cos_id + '%')).all()
    elif len(stu_id) > 0:
        results = Score.query.filter(Score.Stu_id.like('%' + stu_id + '%')).all()
    else:
        results = Score.query.all()
    return json.dumps(results, default=Score.to_json)


@api_blueprint.route('/score', methods=['DELETE'])
def delete_score():
    cos_id = request.values.get('Cos_id')
    stu_id = request.values.get('Stu_id')
    if cos_id is None or stu_id is None:
        return json.dumps({
            'status': 'error',
            'msg': 'Cos_id or Stu_id is missing'
        }), 400
    row = Score.query.filter(Score.Cos_id == cos_id).filter(Score.Stu_id == stu_id).first()
    if row is None:
        return json.dumps({
            'status': 'error',
            'msg': 'target record is not found'
        }), 404
    try:
        db.session.delete(row)
        db.session.commit()
    except Exception as e:
        print(e)
        return json.dumps({
            'status': 'error',
            'msg': 'db operation error'
        }), 500
    return json.dumps({
        'status': 'success'
    })


@api_blueprint.route('/score/<string:cos_id>/<string:stu_id>', methods=['PUT'])
def modify_score(cos_id, stu_id):
    score = request.values.get('Score')
    if score is None:
        return json.dumps({
            'status': 'error',
            'msg': 'Score is missing'
        }), 400
    row = Score.query.filter(Score.Cos_id == cos_id).filter(Score.Stu_id == stu_id).first()
    if row is None:
        return json.dumps({
            'status': 'error',
            'msg': 'target score is not found'
        }), 404
    row.Score = score
    try:
        db.session.commit()
    except Exception as e:
        print(e)
        return json.dumps({
            'status': 'error',
            'msg': 'db operation error'
        }), 500
    return json.dumps({
        'status': 'success'
    })


@api_blueprint.route('/score/upload', methods=['POST'])
def upload_score_file():
    file = request.files['file']
    print(file.filename)
    xls_path = SaveFile.save_xls(file)
    if xls_path == 'error':
        return json.dumps({
            'status': 'error',
            'msg': 'file type is invalid'
        }), 500
    #     开始正确的成绩文件处理
    try:
        workbook = xlrd.open_workbook(os.path.join(os.getcwd(), 'files', xls_path))
        sheet = workbook.sheets()[0]
        col_cos_id = sheet.col_values(0)
        col_stu_id = sheet.col_values(1)
        col_score = sheet.col_values(2)
    except Exception as e:
        print(e)
        return json.dumps({
            'status': 'error',
            'msg': 'process xls file failed'
        }), 500

    # 判断xls文件格式是否正确
    if len(col_cos_id) == len(col_stu_id) and len(col_stu_id) == len(col_score):
        for i in range(len(col_cos_id)):
            print('record: ', col_cos_id[i], col_stu_id[i], col_score[i])
            row = Score.query.filter(Score.Cos_id == col_cos_id[i]).filter(Score.Stu_id == col_stu_id[i]).first()
            if row is None:
                row = Score()
                row.Cos_id = col_cos_id[i]
                row.Stu_id = col_stu_id[i]
                try:
                    row.Score = float(col_score[i])
                except ValueError as e:
                    print(e)
                    continue
                print(row.to_json())
                try:
                    print('add: ', col_cos_id[i], col_stu_id[i], col_score[i])
                    db.session.add(row)
                    # db.session.commit()
                except Exception as e:
                    print('exception:', e)
                continue
            row.Score = col_score[i]
            print(col_cos_id[i], col_stu_id[i], col_score[i])
        db.session.commit()
    else:
        return json.dumps({
            'status': 'error',
            'msg': 'xls file format wrong'
        }), 500

    return json.dumps({
            'status': 'success',
            'msg': 'all records are imported'
        })
